package com.neuedu.my12306.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.neuedu.my12306.dao.DBHelper;
import com.neuedu.my12306.dao.IStationInfoDao;
import com.neuedu.my12306.entity.StationInfo;

public class IStationInfoDaoImpl implements IStationInfoDao {

	@Override
	public boolean insert(StationInfo stationInfo) {
		boolean ret = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "insert into Station (STATIONID, STATIONNAME, STATIONPROVINCE, STATIONIMAGE) values (SEQ_STATION_ID.nextval,?,?,?)"; 
		conn = DBHelper.openConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, stationInfo.getStationName());
			pstmt.setString(2, stationInfo.getStationProvince());
			pstmt.setString(3,stationInfo.getStationImage());
			
			int count = pstmt.executeUpdate();
			if (count==1) {
				ret = true;
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pstmt, conn);
		}
		
		
		return ret;
	}

	@Override
	public int findTotal() {
		int total = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "select count(*) from station";
		conn = DBHelper.openConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			res = pstmt.executeQuery();
			if(res.next()) {
				total = res.getInt(1);//查询记录总数的sql返回一行一列数据，即返回记录总数
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pstmt, conn);
		}
		
		return total;
	}

	@Override
	public List<StationInfo> findStationInfos(int page, int size) {
		List<StationInfo> list = new ArrayList<StationInfo>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "select STATIONID, STATIONNAME, STATIONPROVINCE, STATIONIMAGE"
				+ " from ( select t.*,rownum rn from station t  where rownum <=?) where rn >? ";
		conn = DBHelper.openConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, page*size);
			pstmt.setInt(2, (page-1)*size);
			res = pstmt.executeQuery();
			while (res.next()) {
				int id = res.getInt("STATIONID");
				String stationName = res.getString("STATIONNAME");
				String stationProvince = res.getString("STATIONPROVINCE");
				String stationImage = res.getString("STATIONIMAGE");
				StationInfo stationInfo = new StationInfo(id, stationName, stationProvince, stationImage);
				list.add(stationInfo);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pstmt, conn);
		}
		
		return list;
	}

	@Override
	public boolean deleteByIds(int[] ids) {
		boolean ret = false;
		Connection conn = DBHelper.openConnection();
		PreparedStatement pstmt=null ;
		ResultSet rs = null;
		String sql =  "delete from station where STATIONID=?";
		
		try {
			pstmt = conn.prepareStatement(sql);
			for (int id : ids) {
				pstmt.setInt(1, id);
				pstmt.addBatch();
			}
			int []counts = pstmt.executeBatch();
			int success=0;
			for (int c : counts) {
				if(c==Statement.SUCCESS_NO_INFO||c>0) {
					success++;
				}
			}
			System.out.println("成功删除"+success+"条数据！");
			ret = true;
		} catch (SQLException e) {
			
			
		}finally {
			DBHelper.closeAll(rs, pstmt, conn);
		}
		
		return ret;
	}

	@Override
	public StationInfo findStationInfoById(int id) {
		StationInfo stationInfo=null;
		Connection conn = null;
		ResultSet re = null;
		PreparedStatement pstmt = null;
		String sql = "select STATIONID, STATIONNAME, STATIONPROVINCE, STATIONIMAGE from station where STATIONID=? ";
		
		try {
			conn = DBHelper.openConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			re = pstmt.executeQuery();
			if (re.next()) {
				
				//int uid = re.getInt("STATIONID");
				String stationName = re.getString("STATIONNAME");
				String stationProvince = re.getString("STATIONPROVINCE");
				String stationImage = re.getString("STATIONIMAGE");
				stationInfo = new StationInfo(id, stationName, stationProvince, stationImage);
				
				
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(re, pstmt, conn);
		}
		
		
		return stationInfo;
	}

	@Override
	public boolean editStationInfo(StationInfo stationInfo) {
		boolean ret = false;
		Connection conn=null;
		ResultSet res = null;
		PreparedStatement pmt = null;
		String sql="update  station set STATIONNAME=?, STATIONPROVINCE=?, STATIONIMAGE=? where STATIONID=?";
		
		try {
			conn = DBHelper.openConnection();
			pmt = conn.prepareStatement(sql);
			pmt.setString(1, stationInfo.getStationName());
			pmt.setString(2, stationInfo.getStationProvince());
			pmt.setString(3, stationInfo.getStationImage());
			pmt.setInt(4, stationInfo.getStationId());
			int count = pmt.executeUpdate();
			if (count == 1) {
				ret = true;
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(res, pmt, conn);
		}
		return ret;
	}
	
	
}
